using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using Syncfusion.Windows.Forms.Grid;
using Syncfusion.Windows.Forms.Tools;

namespace ctsu.Admin.Forms
{
	public class Utils
	{
		public static DataTable GetTableSchema(string tableName, string sqlStr)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "";
			if (tableName != null)
				sql = "SELECT * FROM " + tableName + " WHERE 1=2";
			else
			{
				sql = sqlStr;
				if (sql.IndexOf("WHERE") > -1)
					sql += " AND 1=2";
				else
					sql += " WHERE 1=2";
			}

			string conStr = ConfigurationManager.ConnectionStrings["CreamConnectString"].ToString();
			SqlConnection conn = null;
			SqlCommand cmd = null;
			SqlDataAdapter da = null;
			try
			{
				conn = new SqlConnection(conStr);
				cmd = new SqlCommand(sql, conn);
				da = new SqlDataAdapter(cmd);

				DataTable dt = new DataTable();
				da.FillSchema(dt, SchemaType.Source);

				return dt;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retieve table schema for table: " + tableName);
				sb.Append(Environment.NewLine);
				sb.Append("Error: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				throw new Exception(sb.ToString());
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
				if (cmd != null)
					cmd.Dispose();
				if (da != null)
					da.Dispose();
			}
		}

		public static int GetLeftCenterCoord(int width)
		{
			Rectangle rect = SystemInformation.WorkingArea;
			return (int)((rect.Width - width) / 2);
		}

		public static bool IsMissingValue(Control ctl)
		{
			string val = ctl.Text.Trim();
			if (val.Length == 0)
				return true;

			return false;
		}//private static bool IsMissingValue(Control ctl)

		public static bool IsValidDateTime(string text)
		{
			try
			{
				DateTime.Parse(text);
			}
			catch
			{
				return false;
			}
			return true;
		}//private static bool IsValidDateTime(string text)

		public static void LoadCbo(Syncfusion.Windows.Forms.Tools.ComboBoxAutoComplete ctl, String tableName, bool useObject)
		{
			ComboBoxAutoComplete cbo = (ComboBoxAutoComplete)ctl;
			ArrayList al = new ArrayList();

			string sql = "SELECT * FROM " + tableName +
				" ORDER BY Display";

			//string conStr = ConfigurationManager.ConnectionStrings["CreamConnectString"];
			//SqlConnection conn = new SqlConnection(conStr);
			IDataReader reader = null;
			try
			{
				reader = Data.DataAccess.GetDataReader(sql);

				cbo.BeginUpdate();

				int ord;
				string sVal;
				int iVal;
				NameVal nv;
				while (reader.Read())
				{
					ord = reader.GetOrdinal("Display");
					if (reader.IsDBNull(ord))
						sVal = "";
					else
						sVal = reader.GetString(ord);

					ord = reader.GetOrdinal("ID");
					iVal = reader.GetInt32(ord);
					nv = new NameVal(iVal, sVal);
					al.Add(nv);
				}

				cbo.DataSource = al;
				cbo.AutoCompleteControl.DataSource = al;
				cbo.DisplayMember = "Name";
				cbo.ValueMember = "Val";
				cbo.EndUpdate();
			}
			catch
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static void LoadCbo(Control ctl, String tableName, bool useObject)
		{
			ComboBox cbo = (ComboBox)ctl;
			ArrayList al = new ArrayList();

			string sql = "SELECT * FROM " + tableName +
				" ORDER BY Display";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;

			try
			{
				reader = Data.DataAccess.GetDataReader(sql);

				cbo.BeginUpdate();
				//cbo.Items.Clear();

				int ord;
				string sVal;
				int iVal;
				NameVal nv;
				while (reader.Read())
				{
					ord = reader.GetOrdinal("Display");
					if (reader.IsDBNull(ord))
						sVal = "";
					else
						sVal = reader.GetString(ord);

					ord = reader.GetOrdinal("ID");
					iVal = reader.GetInt32(ord);
					nv = new NameVal(iVal, sVal);
					al.Add(nv);
				}

				cbo.DataSource = al;
				cbo.DisplayMember = "Name";
				cbo.ValueMember = "Val";
				cbo.EndUpdate();
			}
			catch
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static void LoadCbo(BaseForms.SpecialLookup sl, Control ctl)
		{
			NameVal nv;
			ComboBox cbo = (ComboBox)ctl;
			ArrayList al = new ArrayList();

			string sql = "SELECT " + sl.DisplayField + "," +
				sl.ValueField + " FROM " + sl.TableName;

			if (sl.WhereClause.Length > 0)
				sql += " WHERE " + sl.WhereClause;

			if (sl.OrderField.Length > 0)
				sql += " ORDER BY " + sl.OrderField;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = Data.DataAccess.GetDataReader(sql);
				cbo.BeginUpdate();

				string sVal;
				int iVal;
				while (reader.Read())
				{
					if (reader.IsDBNull(0))
						continue;
					sVal = reader.GetString(0);

					iVal = reader.GetInt32(1);

					Console.WriteLine("Val:" + iVal);
					nv = new NameVal(iVal, sVal);
					al.Add(nv);
				}

				if (sl.InsertBlank)
				{
					nv = new NameVal(-1, "");
					al.Insert(0, nv);
				}

				cbo.DataSource = al;
				cbo.DisplayMember = "Name";
				cbo.ValueMember = "Val";
				cbo.EndUpdate();
			}
			catch
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static void LoadCbo(BaseForms.SpecialLookup sl, GridStyleInfo gsi)
		{
			NameVal nv;
			ArrayList al = new ArrayList();

			string sql = "";

			if (sl.ValueField == sl.DisplayField)
				sql = "SELECT " + sl.DisplayField +
					" FROM " + sl.TableName;
			else
				sql = "SELECT " + sl.DisplayField + " As Name," +
					sl.ValueField + " FROM " + sl.TableName;


			if (sl.WhereClause.Length > 0)
				sql += " WHERE " + sl.WhereClause;

			if (sl.OrderField.Length > 0)
				sql += " ORDER BY " + sl.OrderField;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = Data.DataAccess.GetDataReader(sql);

				string sVal;
				int iVal;
				while (reader.Read())
				{
					if (sl.ValueField == sl.DisplayField)
					{
						iVal = reader.GetInt32(0);
						sVal = iVal.ToString();
					}
					else
					{
						if (reader.IsDBNull(0))
							continue;
						sVal = reader.GetString(0);

						iVal = reader.GetInt32(1);
					}
					nv = new NameVal(iVal, sVal);
					al.Add(nv);
				}

				gsi.DataSource = al;
				gsi.DisplayMember = "Name";
				gsi.ValueMember = "Val";
				gsi.DropDownStyle = GridDropDownStyle.Exclusive;
			}
			catch
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static void LoadCbo(BaseForms.SpecialLookup sl, GridBoundColumn gbc)
		{
			NameVal nv;
			ArrayList al = new ArrayList();

			string sql = "SELECT " + sl.DisplayField + "," +
				sl.ValueField + " FROM " + sl.TableName;

			if (sl.WhereClause.Length > 0)
				sql += " WHERE " + sl.WhereClause;

			if (sl.OrderField.Length > 0)
				sql += " ORDER BY " + sl.OrderField;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = Data.DataAccess.GetDataReader(sql);

				string sVal;
				int iVal;
				while (reader.Read())
				{
					if (reader.IsDBNull(0))
						continue;
					sVal = reader.GetString(0);

					iVal = reader.GetInt32(1);
					nv = new NameVal(iVal, sVal);
					al.Add(nv);
				}

				gbc.StyleInfo.DataSource = al;
				gbc.StyleInfo.DisplayMember = "Name";
				gbc.StyleInfo.ValueMember = "Val";
				gbc.StyleInfo.DropDownStyle = GridDropDownStyle.Exclusive;
			}
			catch
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static void LoadCbo(Control ctl, String tableName)
		{
			ComboBox cbo = (ComboBox)ctl;

			string sql = "SELECT * FROM " + tableName +
				" ORDER BY Display";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			DataTable dt = null;

			try
			{
				DataSet ds = Data.DataAccess.GetDataSet(sql);
				dt = ds.Tables[0];

				cbo.BeginUpdate();
				cbo.DataSource = dt;
				cbo.DisplayMember = "Display";
				cbo.ValueMember = "Val";
				cbo.EndUpdate();
				cbo.SelectedIndex = -1;
			}
			catch
			{
				throw;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();				
			}

		}

		public static Control GetControlByName(string ctlName, Control.ControlCollection ctls)
		{
			Control retCtl = null;
			foreach (Control ctl in ctls)
			{
				if (ctl.Controls.Count > 0)
				{
					retCtl = GetControlByName(ctlName, ctl.Controls);
					if (retCtl != null)
						return retCtl;
				}

				if (ctl.Name.Equals(ctlName))
					return ctl;
			}
			return retCtl;
		}

		public static Control GetControlByFieldName(string fieldName, Control.ControlCollection ctls, string[] asExclude)
		{
			//Control must be named with a 3 letter prefix followed
			//by the field name
			string ctlName = "";
			Control retCtl = null;
			bool isExclude = false;

			foreach (Control ctl in ctls)
			{
				if (ctl.GetType().ToString() == "Syncfusion.Windows.Forms.Grid.GridControl")
					continue;
				isExclude = false;
				if (ctl.Controls.Count > 0)
				{
					retCtl = GetControlByFieldName(fieldName, ctl.Controls, asExclude);
					if (retCtl != null)
						return retCtl;
				}

				if (ctl.GetType().ToString() == "System.Windows.Forms.UpDownBase+UpDownEdit" || ctl.GetType().ToString() == "System.Windows.Forms.UpDownBase+UpDownButtons")
					ctlName = ctl.Parent.Name;
				else
					ctlName = ctl.Name;

				foreach (string s in asExclude)
				{
					if (ctlName.StartsWith(s))
					{
						isExclude = true;
						break;
					}
				}

				if (isExclude)
					continue;

				if (ctlName.Substring(3).Equals(fieldName))
					return ctl;
			}
			return retCtl;
		}

		public static int GetColWidth(int len, string text, Control ctl)
		{
			Graphics gfx = ctl.CreateGraphics();
			string s = new string('M', len);
			SizeF sf0 = gfx.MeasureString(s, ctl.Font);
			SizeF sf1 = gfx.MeasureString(text, ctl.Font);

			int max = (int)Math.Max(sf0.Width, sf1.Width + 6);
			gfx.Dispose();

			return max;
		}

		public static void txtNumWholePos_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			int ic = (int)e.KeyChar;
			if (ic == 8 || ic == 27)
				return;
			string k = e.KeyChar.ToString();
			string s = "1234567890";

			if (s.IndexOf(k) == -1)
			{
				e.Handled = true;
				return;
			}
		}

		public static void txtNumWholeNeg_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			Control ctl = (Control)sender;
			int ic = (int)e.KeyChar;
			if (ic == 8 || ic == 27)
				return;
			string k = e.KeyChar.ToString();
			string s = "1234567890-";

			if (s.IndexOf(k) == -1)
			{
				e.Handled = true;
				return;
			}

			if (ctl.Text.Length > 0)
			{
				if (e.KeyChar == '-')
				{
					e.Handled = true;
					return;
				}
			}
		}

		public static void txtNumDecPos_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			Control ctl = (Control)sender;

			int ic = (int)e.KeyChar;
			if (ic == 8 || ic == 27)
				return;

			string k = e.KeyChar.ToString();
			string s = "1234567890.";
			if (ctl.Text.IndexOf(".") > -1)
			{
				if (e.KeyChar == '.')
				{
					e.Handled = true;
					return;
				}
			}


			if (s.IndexOf(k) == -1)
			{
				e.Handled = true;
				return;
			}
		}

		public static void txtNumDecNeg_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			Control ctl = (Control)sender;
			int ic = (int)e.KeyChar;
			if (ic == 8 || ic == 27)
				return;
			string k = e.KeyChar.ToString();
			string s = "1234567890-.";

			if (ctl.Text.IndexOf(".") > -1)
			{
				if (e.KeyChar == '.')
				{
					e.Handled = true;
					return;
				}
			}

			if (ctl.Text.Length > 0)
			{
				if (e.KeyChar == '-')
				{
					e.Handled = true;
					return;
				}
			}

			if (s.IndexOf(k) == -1)
			{
				e.Handled = true;
				return;
			}
		}

		public static void txtDate_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			int ic = (int)e.KeyChar;
			if (ic == 8 || ic == 27)
				return;
			string k = e.KeyChar.ToString();
			string s = "1234567890/";

			if (s.IndexOf(k) == -1)
			{
				e.Handled = true;
				return;
			}
		}

	}

	public class NameVal
	{
		private int val;
		private string sVal;
		private string name;
		private string originalName;
		private bool isChanged = false;

		public NameVal()
		{
		}

		public NameVal(int val, string name)
		{
			this.val = val;
			this.name = name;
		}

		public int Val
		{
			get
			{
				return val;
			}

			set
			{
				val = value;
			}
		} //public int Val

		public string StringValue
		{
			get { return sVal; }
			set { sVal = value; }
		} //public string Name


		public string Name
		{
			get { return name; }
			set { name = value; }
		} //public string Name

		public string OriginalName
		{
			get { return originalName; }
			set { originalName = value; }
		}

		public bool IsChanged
		{
			get
			{
				return isChanged;
			}

			set
			{
				isChanged = value;
			}
		}

		public override string ToString()
		{
			return name;
		}
	}//public class NameVal


	public class DataUtils
	{
		private static Hashtable htOleDbParameterType;
		private static Hashtable htSqlDbParameterType;

		public DataUtils()
		{

		}

		//static constructor
		static DataUtils()
		{
			InitializeHashTables();
		}

		//no joins		
		public static string GetSqlInsertStatment(DataTable dataTable, String[] aExclude, string tableName)
		{
			bool isExclude = false;
			StringBuilder sb = new StringBuilder("INSERT INTO " + tableName + " (");
			StringBuilder sbValue = new StringBuilder("Values(");

			foreach (DataColumn dc in dataTable.Columns)
			{
				if (aExclude != null)
				{
					for (int i = 0; i < aExclude.Length; i++)
					{
						if (dc.ColumnName.Equals(aExclude[i]))
						{
							isExclude = true;
							break;
						}
					}
				}

				if (isExclude)
				{
					isExclude = false;
					continue;
				}

				if (dc.AutoIncrement)
					continue;

				sb.Append(dc.ColumnName + ",");
				sbValue.Append("@" + dc.ColumnName + ",");

			}
			//remove last ,
			sb.Remove(sb.Length - 1, 1);
			//add ending bracket
			sb.Append(") ");
			//remove last ,
			sbValue.Remove(sbValue.Length - 1, 1);
			sbValue.Append(") ");
			//add ending bracket
			sb.Append(sbValue.ToString());

			return sb.ToString();
		}

		public static string GetWhereStatement(ArrayList alKeys)
		{
			StringBuilder sb = new StringBuilder("WHERE ");
			int count = 0;
			foreach (string s in alKeys)
			{
				if (count == 0)
					sb.Append(s + "=");
				else
					sb.Append(" AND " + s + "=");

				sb.Append("@" + s);
			}
			return sb.ToString();
		}

		public static string GetSqlUpdateStatment(DataTable dataTable, String[] aExclude, string tableName)
		{
			bool isExclude = false;
			StringBuilder sb = new StringBuilder("UPDATE " + tableName + " SET ");

			foreach (DataColumn dc in dataTable.Columns)
			{
				if (aExclude != null)
				{
					for (int i = 0; i < aExclude.Length; i++)
					{
						if (dc.ColumnName.Equals(aExclude[i]))
						{
							isExclude = true;
							break;
						}
					}
				}

				if (isExclude)
				{
					isExclude = false;
					continue;
				}

				if (IsKey(dc.ColumnName, dataTable))
				{
					isExclude = false;
					continue;
				}

				sb.Append(dc.ColumnName + "=");
				sb.Append("@" + dc.ColumnName + ",");
			}
			//remove last ,
			sb.Remove(sb.Length - 1, 1);
			return sb.ToString();
		}

		public static SqlParameter[] GetSqlParameters(String[] aExclude, bool includeKey, DataTable dataTable)
		{
			bool isExclude = false;
			bool isKey = false;
			int size = 0;

			SqlParameter param;

			ArrayList alInclude = new ArrayList();
			//first count the number of params by putting them in 
			//an arrayList so that we can init the array
			foreach (DataColumn dc in dataTable.Columns)
			{
				isKey = false;
				if (aExclude != null)
				{
					for (int i = 0; i < aExclude.Length; i++)
					{
						if (dc.ColumnName.Equals(aExclude[i]))
						{
							isExclude = true;
							break;
						}
					}
				}

				if (isExclude)
				{
					isExclude = false;
					continue;
				}

				if (IsKey(dc.ColumnName, dataTable))
					isKey = true;

				if (!includeKey && isKey)
					continue;

				alInclude.Add(dc);
			}
			SqlParameter[] aParams = new SqlParameter[alInclude.Count];

			int j = 0;
			foreach (DataColumn dc in alInclude)
			{
				if (dc.MaxLength < 0)
					size = 0;
				else
					size = dc.MaxLength;

				param = new SqlParameter("@" + dc.ColumnName, (SqlDbType)GetParameterType(dc.DataType.ToString()), size, dc.ColumnName);

				aParams[j] = param;
				j++;
			}

			return aParams;
		}

		public static void GetCommandParameters(IDbCommand cmd, String[] aExclude, bool includeKey, DataTable dataTable)
		{
			bool isExclude = false;
			bool isKey = false;
			int size = 0;
			IDataParameter param;

			try
			{
				foreach (DataColumn dc in dataTable.Columns)
				{
					isKey = false;
					if (aExclude != null)
					{
						for (int i = 0; i < aExclude.Length; i++)
						{
							if (dc.ColumnName.Equals(aExclude[i]))
							{
								isExclude = true;
								break;
							}
						}
					}

					if (isExclude)
					{
						isExclude = false;
						continue;
					}

					if (IsKey(dc.ColumnName, dataTable))
						isKey = true;

					if (!includeKey && isKey)
						continue;

					if (dc.MaxLength < 0)
						size = 0;
					else
						size = dc.MaxLength;

					param = new SqlParameter("@" + dc.ColumnName, (SqlDbType)GetParameterType(dc.DataType.ToString()), size, dc.ColumnName);
					cmd.Parameters.Add(param);

				}
			}
			catch
			{
			}
		}

		public static void GetCommandParameters(IDbCommand cmd, String[] fields, DataTable dataTable)
		{
			int size = 0;
			IDataParameter param;

			try
			{
				foreach (string field in fields)
				{
					if (!dataTable.Columns.Contains(field))
						continue;

					DataColumn dc = dataTable.Columns[field];
					if (dc.MaxLength < 0)
						size = 0;
					else
						size = dc.MaxLength;

					param = new SqlParameter("@" + dc.ColumnName, (SqlDbType)GetParameterType(dc.DataType.ToString()), size, dc.ColumnName);
					cmd.Parameters.Add(param);

				}
			}
			catch
			{
			}
		}

		public static void GetCommandParameters(IDbCommand cmd, String[] aExclude, bool includeKey, DataTable dataTable, Hashtable htKeys, ArrayList alKeys)
		{
			bool isExclude = false;
			int size = 0;
			IDataParameter param;

			try
			{
				foreach (DataColumn dc in dataTable.Columns)
				{
					if (aExclude != null)
					{
						for (int i = 0; i < aExclude.Length; i++)
						{
							if (dc.ColumnName.Equals(aExclude[i]))
							{
								isExclude = true;
								break;
							}
						}
					}

					if (isExclude)
					{
						isExclude = false;
						continue;
					}

					//skip keys for now
					if (IsKey(dc.ColumnName, dataTable))
						continue;

					if (dc.MaxLength < 0)
						size = 0;
					else
						size = dc.MaxLength;

					param = new SqlParameter("@" + dc.ColumnName, (SqlDbType)GetParameterType(dc.DataType.ToString()), size, dc.ColumnName);
					cmd.Parameters.Add(param);
				}
				if (includeKey)
				{
					//do order for OleDb sake
					foreach (string s in alKeys)
					{
						DataColumn dcK = (DataColumn)htKeys[s];
						param = new SqlParameter("@" + dcK.ColumnName, (SqlDbType)GetParameterType(dcK.DataType.ToString()), size, dcK.ColumnName);
						cmd.Parameters.Add(param);
					}
				}
			}
			catch
			{
			}
		}

		private static int GetParameterType(String systemType)
		{
			return (int)htSqlDbParameterType[systemType];
		}

		//the schema must be included for dataTable
		public static bool IsKey(string field, DataTable dt)
		{
			DataColumn[] colArray;
			if (dt.Columns[field].AutoIncrement)
				return true;

			if (dt.PrimaryKey != null)
			{
				colArray = dt.PrimaryKey;
				foreach (DataColumn dc in colArray)
				{
					if (dc.ColumnName.Equals(field))
						return true;
				}
			}
			return false;
		}

		private static void InitializeHashTables()
		{
			htOleDbParameterType = new Hashtable();

			htOleDbParameterType.Add("System.String", OleDbType.VarWChar);
			htOleDbParameterType.Add("System.DateTime", OleDbType.DBTimeStamp); //.DBDate);
			htOleDbParameterType.Add("System.Int32", OleDbType.Integer);
			htOleDbParameterType.Add("System.Double", OleDbType.Double);
			htOleDbParameterType.Add("System.Boolean", OleDbType.Boolean);
			htOleDbParameterType.Add("System.Decimal", OleDbType.Decimal);

			htSqlDbParameterType = new Hashtable();

			htSqlDbParameterType.Add("System.String", SqlDbType.VarChar);
			htSqlDbParameterType.Add("System.DateTime", SqlDbType.DateTime); //.DBDate);
			htSqlDbParameterType.Add("System.Int32", SqlDbType.Int);
			htSqlDbParameterType.Add("System.Double", SqlDbType.Float);
			htSqlDbParameterType.Add("System.Boolean", SqlDbType.Bit);
			htSqlDbParameterType.Add("System.Decimal", SqlDbType.Decimal);
		}

	}
}
